'''
日期：2020-5-25
功能：基于日常工作记录表做的故障分析工具
    自动生成按月的分类统计表并且生成初步的可视化图表
'''
import pandas as pd
import numpy as np
from pandas import DataFrame,Series
from collections import Counter
import datetime,os
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

now=datetime.datetime.now()
strf=now.strftime('%Y-%m-%d')

def work_dir(file_dir):
    for root,dirs,files in os.walk(file_dir):
        # print("root : {0}".format(root)) #当前根目录
        # print("dirs : {0}".format(dirs))  #当前目录下的文件夹
        print("files : {0}".format(files)) #当前目录下的文件列表
        for file in files:
            try:
                file_name=os.path.splitext(file)[0]
                file_suffix=os.path.splitext(file)[1]
                file_path=os.path.join(root,file)
                file_abs_path=os.path.abspath(file)
                file_parent=os.path.dirname(file_path)

                print("file:{0}".format(file))
                # print("file_name : {0}".format(file_name))
                # print("file_suffix : {0}".format(file_suffix))
                # print("file_path : {0}".format(file_path))
                # print("file_abs_path : {0}".format(file_abs_path))
                # print("file_parent : {0}".format(file_parent))
            except Exception as e:
                print('Exception',e)
        return files
files=work_dir('./')

def judge_suffix(files):
    file=''
    for i in range(len(files)):
        if os.path.splitext(files[i])[1] != '.xlsx':
            pass
        else:
            file=files[i]        
    return file
file=judge_suffix(files)
print(file)
#在当前目录下创建文件夹
def mkdir(path):
    folder=os.path.exists(path)
    if not folder:
        os.mkdir(path)
    else:
        print(path,'文件夹存在')
fl_name='./final'
mkdir(fl_name)
class OperatExcel(object):
    
    def __init__(self,name):
        self.name=name
        self.data=pd.read_excel(name)
        self.df=DataFrame(self.data)    
    def ReadExcel(self):
        df=self.df
        # df=df1.drop(labels=0)  #删除首行
        # print(df.columns)
        return df
    def shape(self):
        df=self.df
        print(df.shape[0],df.shape[1])
        return self.df.shape
    def ExchangeRc(self):
        df=self.df
        self.df=pd.DataFrame(df.values.T, index=df.columns, columns=df.index)

    def WriteExcel(self,data,filename,sheetname):
        self.filename=filename
        self.sheetname=sheetname
        write=pd.ExcelWriter(filename)
        self.df=DataFrame(data)
        df=self.df
        # df=DataFrame.from_dict(data)    #data为字典数据
        df=pd.DataFrame(df.values.T, index=df.columns, columns=df.index)    #行列转置
        print(now,"写入数据开始！")
        df.to_excel(excel_writer=write,sheet_name=sheetname)
        write.save()
        write.close()
        print(now,"完成写入数据！")

#合并列表
#arg_list:需要合并的列表
#new_list:合并后的列表
def merge_list(arg_list):
    new_list=[]
    for i in arg_list:
        if type(i) is not list:
            new_list.append(i)
        else:
            new_list.extend(i)
    # print('new_list:',new_list)
    return Counter(new_list)

#合并字典数据

def merge_dict(arg_list,dataframe):
    dict_list={}
    dict_list=dict_list.fromkeys(arg_list, 1)
    for i in range(len(arg_list)):
        # print('arg_list:',arg_list[i])
        
        values_list=dataframe.loc[arg_list[i]].values.tolist()
        temp=dict(merge_list(values_list))
        dict_list[arg_list[i]]=temp
        # print('temp:',temp)    
    # print(dict_list.keys())
    # print(dict_list.values())
    print('dict_list:',dict_list)
    return dict_list

inst=OperatExcel(file)
data=inst.ReadExcel()

date=pd.to_datetime(data['日期'])
data['日期']=date.dt.strftime('%Y-%m')
data2= data[["日期","问题归属"]]   #筛选需要的列
# filt_data=pd.DataFrame(filt_data.values.T, index=filt_data.columns, columns=filt_data.index)
# data2=pd.DataFrame(data2.values.T, index=data2.columns, columns=data2.index)
keys=sorted(list(set(data2['日期'])))
# values=list(data2['问题归属'])
data2=data2.set_index('日期')
# print(Counter(data2['2017-01':'2017-02']['问题归属'].values.tolist()))
# print(data2['2017-01':'2017-02'])
# print(data2.iloc[1:2])
# print(data2.loc['2017-01'])  #测试按索引筛选数据
# inst.WriteExcel(data2,"./1213.xlsx","sheet1213")
print('keys:',keys)

(file1,extension)=os.path.splitext(file)
f_name='./'+file1+'分析表'+str(strf)+'.xlsx'
# print(f_name)
analyse_dict=merge_dict(keys,data2)
os.chdir('./'+fl_name)
inst.WriteExcel(analyse_dict,f_name,'数据统计表')
inst2=OperatExcel(f_name)
data3=inst2.ReadExcel()
# print(data3.columns)
data3=data3.set_index('Unnamed: 0')
# print(data3)
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus']=False #用来正常显示负号
data3.plot(kind='bar',width=1,figsize=(12,6))
ax = plt.gca()  # gca stands for 'get current axis'
ax.spines['right'].set_color('none')  # 设置右‘脊梁’为无色
ax.spines['top'].set_color('none')  # 设置上‘脊梁’为无色
ax.xaxis.set_ticks_position('bottom')  # 底部‘脊梁’设置为X轴
ax.spines['bottom'].set_position(('data', 0))  # 底部‘脊梁’移动位置，y的data
ax.yaxis.set_ticks_position('left')  # 左部‘脊梁’设置为Y轴
ax.spines['left'].set_position(('data', -0.8))  # 左部‘脊梁’移动位置，x的data
# plt.figure(figsize=(3,3))
plt.xlabel('年月')
plt.ylabel('起')
plt.title('工作记录表分析图')
plt.legend(loc="upper right")   #图例位置
plt.grid(True)  #网格线

picture='./'+str(strf)+'.png'
picture2='./'+str(strf)+'.pdf'
plt.savefig(picture)
plt.savefig(picture2)

workbook=load_workbook(f_name)
sheet=workbook.worksheets[0]
image=Image(picture)
# newsize = (800, 600)
# image.width, image.height = newsize  #分别对应插入图片的宽和高
sheet.add_image(image,'A20')
# sheet.column_dimensions['A'].width = 20.0
# sheet.row_dimensions[20].height = 40.0      #分别修改A20单元格的行高和列宽
workbook.save(f_name)
plt.show()





